package com.ncloud.act.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.alibaba.fastjson.JSONObject;
import com.ncloud.act.entity.ChnlDeposit;

@Mapper
public interface ChnlDepositMapper{

	@Select("SELECT chnl_id, deposit_money, credit_value, state,date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id,date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_deposit "
			+ "where chnl_id =#{chnl_id} ")
	@Results({
		@Result(column="chnl_id",property="chnl_id"),@Result(column="deposit_money",property="deposit_money"),
		@Result(column="credit_value",property="credit_value"),@Result(column="state",property="state"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlDeposit> getChnlDepositByChnlID(Long chnl_id);
	
	@Select("SELECT t1.chnl_id, ifnull(t2.deposit_money,0) as deposit_money, ifnull(t2.credit_value,0) as credit_value, t1.chnl_name,t1.chnl_type,t1.parent_id, "
			+ "(select ifnull(sum(ifnull(balance,0)),0) from sys_chnl_bill t where t.chnl_id = t1.chnl_id and t.cycle_id =#{0}) as balance,"
			+ "(select ifnull(sum(ifnull(balance,0)),0) from sys_chnl_bill t where t.chnl_id = t1.chnl_id and t.cycle_id !=#{0}) as historybalance "
			+ "FROM sys_chnl t1 left join sys_chnl_deposit t2 on t1.chnl_id=t2.chnl_id "
			/*+ "where t1.chnl_id in (SELECT chnl_id FROM  sys_chnl where chnl_id = #{1} "
			+ "union all "
			+ "SELECT chnl_id FROM sys_chnl where parent_id = #{1}) "*/
			+ "where FIND_IN_SET(t1.chnl_id,f_getChnlList(#{1}))")
	@Results({
		@Result(javaType=String.class,column="chnl_id",property="chnl_id"),@Result(column="deposit_money",property="deposit_money"),
		@Result(column="credit_value",property="credit_value"),@Result(column="chnl_name",property="chnl_name"),
		@Result(column="chnl_type",property="chnl_type"),@Result(javaType=String.class,column="parent_id",property="parent_id"),
		@Result(column="balance",property="balance"),@Result(column="historybalance",property="historybalance")})
	List<JSONObject> getChnlDepositInfo(Integer bill_cycle_id,Integer user_id);
	
	@Insert(value = "INSERT INTO sys_chnl_deposit (chnl_id, deposit_money, credit_value, state, create_time, create_user_id, update_time, update_user_id, remark) "
										+ "values(#{chnl_id},#{deposit_money},#{credit_value},#{state},#{create_time},#{create_user_id},#{update_time},#{update_user_id},#{remark})")
    public int insertChnlDeposit(ChnlDeposit chnlDeposit);
	
	@Update(value = "UPDATE sys_chnl_deposit SET deposit_money = #{deposit_money},update_time = #{update_time}, update_user_id = #{update_user_id} WHERE chnl_id = #{chnl_id} ")
    public int updateChnlDeposit(ChnlDeposit chnlDeposit);
}
